﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using ConnectLib.DAO;
using MSSQLProvider.DataBase;

namespace ConnectLib.DAL
{
    public class IntroductionDAL : MSSQLDataAccess
    {
        private static IntroductionDAL _instance = new IntroductionDAL();

        public static IntroductionDAL Instance
        {
            get
            {
                return _instance;
            }
        }

        public IntroductionDAL()
        {
        }

        public bool Exist(byte intid)
        {
            if (ExecuteScalar(string.Format("SELECT [IntId] FROM [Introduction] WHERE [IntId] = {0}", intid)) == null)
            {
                return false;
            }

            return true;
        }
        
        public int AddIntroduction(IntroductionDAO intro)
        {
            try
            {
                if (Exist(intro.IntId))
                {
                    return 0;
                }
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("IntId");
                arrParamName.Add("@IntId");
                arrValue.Add(intro.IntId);

                arrFielName.Add("HotLine");
                arrParamName.Add("@HotLine");
                arrValue.Add(intro.HotLine);

                arrFielName.Add("FullContent");
                arrParamName.Add("@FullContent");
                arrValue.Add(intro.FullContent);
                
                var queryBuider = new StringBuilder();
                queryBuider.Append("INSERT INTO [Introduction] (");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    queryBuider.Append(arrFielName[i]);
                    if (i < arrFielName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");
                queryBuider.Append(" VALUES (");
                i = 0;

                for (; i < arrParamName.Count; i++)
                {
                    queryBuider.Append(arrParamName[i]);
                    if (i < arrParamName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateIntroduction(IntroductionDAO intro)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("HotLine");
                arrParamName.Add("@HotLine");
                arrValue.Add(intro.HotLine);

                arrFielName.Add("FullContent");
                arrParamName.Add("@FullContent");
                arrValue.Add(intro.FullContent);

                arrFielName.Add("IntId");
                arrParamName.Add("@IntId");
                arrValue.Add(intro.IntId);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Introduction] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IntroductionDAO GetIntroduction(byte intid)
        {
            IntroductionDAO intro = null;

            try
            {
                Hashtable htb = ExecuteHashtable(string.Format("SELECT * FROM [Introduction] WITH (NOLOCK) WHERE IntId = {0}", intid));
                if (htb != null)
                {
                    intro = new IntroductionDAO(htb);
                }
            }
            catch (Exception)
            {
                throw;
            }

            return intro;
        }

        public string GetFullContent(byte intid)
        {
            SqlDataReader dr = null;
            string fullcontent = string.Empty;

            try
            {
                dr = ExecuteReader(string.Format("SELECT TOP 1 [FullContent] FROM [Introduction] WITH (NOLOCK) WHERE IntId = {0}", intid));
                while (dr.Read())
                {
                    fullcontent = dr[0].ToString();
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
            }

            return fullcontent;
        }

        public string GetHotLine()
        {
            SqlDataReader dr = null;
            string hotline = string.Empty;

            try
            {
                dr = ExecuteReader("SELECT [HotLine] FROM [Introduction] WITH (NOLOCK) WHERE [IntId] =1");
                while (dr.Read())
                {
                    hotline = dr[0].ToString();
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
            }

            return hotline;
        }
    }
}
